package com.deepinjava.dbbmlite

abstract class BaseSqlProvider {

    protected val insertSql: String
        get() { return """
        INSERT INTO DBB_PI (ID,SCOL,ICOL,DCOL,DTCREAT,DTUPDATE,TID,SCOLEXT)
            VALUES(?,?,?,?,$sysTimestamp,$sysTimestamp,?,?)
        """.trimIndent()
        }

    protected val sumSqlTemplate ="""
        INSERT INTO dbb_temp_sum
        SELECT SUBSTRING(SCOL,1,#1),COUNT(ID),AVG(DCOL),MAX(ICOL)
            FROM DBB_PI GROUP BY SUBSTRING(SCOL,1,#1) ORDER BY SUBSTRING(SCOL,1,#1)
        """.trimIndent()

    protected open val updateSql : String
        get() { return  """
        UPDATE DBB_PI SET ICOL=ICOL+1,DCOL=DCOL/10, DTUPDATE=$sysTimestamp,TID=CONCAT(TID ,'U')
            WHERE ID BETWEEN ? AND ?
        """.trimIndent()}

    protected val delSql = """
        DELETE FROM DBB_PI WHERE ID BETWEEN ? AND ?
        """.trimIndent()

    protected val delbakSql = """
        DELETE FROM DBB_PI_BAK WHERE ID BETWEEN ? AND ?
        """.trimIndent()

    protected open val writeSql : String
        get() { return  """
        UPDATE DBB_PI SET ICOL=ICOL-1,DCOL=DCOL*10, DTUPDATE=$sysTimestamp,TID=CONCAT(TID ,'W')
            WHERE ID IN (#)
        """.trimIndent()}

    protected val readSql = """
        SELECT * FROM DBB_PI  WHERE ID BETWEEN ? AND ?
        """.trimIndent()

    protected val readOrderSql = """
        SELECT * FROM DBB_PI
            WHERE ID BETWEEN ? AND ? AND ICOL > ? ORDER BY ICOL
        """.trimIndent()

    protected open val readTopSql:String
        get() {return "$readOrderSql $readTop"}

    protected val findSqlTemplate:String
        get(){return """
        INSERT INTO DBB_TEMP_FIND
        SELECT '#1#1#1#1',ID*10+$instrLoc LOC,ID,SCOL
        FROM (SELECT ID, SCOL
              FROM DBB_PI
              WHERE SCOL LIKE '%#1#1#1#1%'
              UNION ALL
              SELECT PN.ID, CONCAT(PN.SCOL, PB.SCOL) SCOL
              FROM (SELECT ID, SCOL FROM DBB_PI WHERE SCOL LIKE '#1%') PB,
                   (SELECT ID, SCOL FROM DBB_PI WHERE SCOL LIKE '%#1#1#1') PN
              WHERE PB.ID = PN.ID + 1
              UNION ALL
              SELECT PN.ID, CONCAT(PN.SCOL, PB.SCOL) SCOL
              FROM (SELECT ID, SCOL FROM DBB_PI WHERE SCOL LIKE '#1#1%') PB,
                   (SELECT ID, SCOL FROM DBB_PI WHERE SCOL LIKE '%#1#1') PN
              WHERE PB.ID = PN.ID + 1
              UNION ALL
              SELECT PN.ID, CONCAT(PN.SCOL, PB.SCOL) SCOL
              FROM (SELECT ID, SCOL FROM DBB_PI WHERE SCOL LIKE '#1#1#1%') PB,
                   (SELECT ID, SCOL FROM DBB_PI WHERE SCOL LIKE '%#1') PN
              WHERE PB.ID = PN.ID + 1
             ) PALL
        ORDER BY PALL.ID
        """.trimIndent()}

    protected val matchSqlTemplate:String
        get(){return """
        INSERT INTO DBB_TEMP_FIND
        SELECT '#1#1#1#1',ID*10 + $instrLoc- 3 LOC,ID, SCOLEXT
            FROM DBB_PI WHERE SCOLEXT LIKE '%#1#1#1#1%'
        """.trimIndent()}

    protected val copySql:String
        = """
        INSERT INTO DBB_PI_BAK
        SELECT * FROM DBB_PI WHERE ID BETWEEN ? AND ? AND ICOL BETWEEN ? AND ?
        """.trimIndent()

    protected val iniDbSql:Array<String>
        get() {return arrayOf(
        "DROP TABLE DBB_PI",
        "DROP TABLE DBB_PI_BAK",
        "DROP TABLE DBB_TEMP_SUM",
        "DROP TABLE DBB_TEMP_FIND",
        """
        CREATE TABLE DBB_PI (
          ID       $defINT      NOT NULL,
          SCOL     CHAR(10)     NOT NULL,
          ICOL     $defBIGINT   NOT NULL,
          DCOL     $defDOUBLE   NOT NULL,
          DTCREAT  $defDATETIME NOT NULL,
          DTUPDATE $defDATETIME NOT NULL,
          TID      VARCHAR(10)  NOT NULL,
          SCOLEXT  VARCHAR(40)  NOT NULL,
          PRIMARY KEY (ID)
        )
        """.trimIndent(),
        """
        CREATE TABLE DBB_PI_BAK (
          ID       $defINT      NOT NULL,
          SCOL     CHAR(10)     NOT NULL,
          ICOL     $defBIGINT   NOT NULL,
          DCOL     $defDOUBLE   NOT NULL,
          DTCREAT  $defDATETIME NOT NULL,
          DTUPDATE $defDATETIME NOT NULL,
          TID      VARCHAR(10)  NOT NULL,
          SCOLEXT  VARCHAR(40)  NOT NULL,
          PRIMARY KEY (ID)
        )
        """.trimIndent(),
        """
        CREATE INDEX DBB_PI_ICOL_INDEX ON DBB_PI (ICOL)
        """.trimIndent(),
//        """
//        CREATE INDEX DBB_PI_BAK_TID_INDEX ON DBB_PI_BAK (tid)
//        """.trimIndent(),
        """
        CREATE TABLE DBB_TEMP_SUM (
          LSCOL    VARCHAR(10) NOT NULL,
          CountID  $defBIGINT  NOT NULL,
          AvgDCOL  $defDOUBLE  NOT NULL,
          MaxICOL  $defBIGINT  NOT NULL
        )
        """.trimIndent(),
        """
        CREATE TABLE DBB_TEMP_FIND (
          FIND    VARCHAR(10) NOT NULL,
          LOC     $defBIGINT  NOT NULL,
          PIID    $defINT     NOT NULL,
          PSCOL   VARCHAR(30) NOT NULL
        )
        """.trimIndent())
        }


    protected abstract val sysTimestamp: String
    protected abstract val instrLoc: String
    protected abstract val readTop: String
    protected abstract val readPage: String
    protected abstract val defINT: String
    protected abstract val defBIGINT: String
    protected abstract val defDOUBLE: String
    protected abstract val defDATETIME: String


    open fun getReadPage (pagesize: Int, offset: Int): String {
        return readPage.replace("#offset", offset.toString())
                .replace("#pagesize", pagesize.toString())
    }

    open fun getFindSql(i: Int): String {
        return findSqlTemplate.replace("#1", i.toString())
    }

    fun getMatchSql(i: Int): String {
        return matchSqlTemplate.replace("#1", i.toString())
    }

    open fun getSumSql(i: Int): String {
        return sumSqlTemplate.replace("#1", i.toString())
    }

    fun getReadPageSql(pagesize: Int, offset: Int): String {
        return readOrderSql + " " + getReadPage(pagesize,offset)
    }

//    open fun getCopySql (pagesize: Int, offset: Int): String{
//        return copySql + " " + getReadPage(pagesize,offset)
//    }
}



